package nix.demo3.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import nix.demo3.domain.Customer;
import nix.demo3.domain.PageBean;
import nix.jdbc.TxQueryRunner;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

public class CustomerDao {

	private QueryRunner qr = new TxQueryRunner();

	public void add(Customer customer) {

		try {
			qr.update("insert into t_customer values (?,?,?,?,?,?,?)",
					customer.getCid(), customer.getCname(),
					customer.getGender(), customer.getBirthday(),
					customer.getCellphone(), customer.getEmail(),
					customer.getDescription());
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public PageBean<Customer> findAll(int pc, int ps) {
		try {
			PageBean<Customer> pb = new PageBean<Customer>();
			pb.setPc(pc);
			pb.setPs(ps);
			Number num = (Number) qr.query("select count(*) from t_customer",
					new ScalarHandler());
			pb.setTr(num.intValue());
			List<Customer> beanList = qr.query(
					"select * from t_customer order by cellphone limit ?, ?",
					new BeanListHandler<Customer>(Customer.class), (pc - 1)
							* ps, ps);
			pb.setBeanList(beanList);
			return pb;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public Customer load(String cid) {
		try {
			return qr.query("select * from t_customer where cid = ?",
					new BeanHandler<Customer>(Customer.class), cid);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public void edit(Customer customer) {
		try {
			qr.update(
					"update t_customer set cname = ?, gender = ?, birthday = ?, cellphone = ?, email = ?, description = ? "
							+ "where cid = ?", customer.getCname(),
					customer.getGender(), customer.getBirthday(),
					customer.getCellphone(), customer.getEmail(),
					customer.getDescription(), customer.getCid());
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public void delete(String cid) {
		try {
			qr.update("delete from t_customer where cid = ?", cid);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public PageBean<Customer> query(Customer criteria, int pc, int ps) {
		try {
			PageBean<Customer> pb = new PageBean<Customer>();
			pb.setPc(pc);
			pb.setPs(ps);

			StringBuilder cntSql = new StringBuilder(
					"select count(*) from t_customer ");
			StringBuilder whereSql = new StringBuilder(" where 1 = 1 ");

			List<Object> params = new ArrayList<Object>();
			if (criteria.getCname() != null
					&& !criteria.getCname().trim().isEmpty()) {
				whereSql.append(" and cname like ? ");
				params.add("%" + criteria.getCname() + "%");
			}
			if (criteria.getGender() != null
					&& !criteria.getGender().trim().isEmpty()) {
				whereSql.append(" and gender = ? ");
				params.add(criteria.getGender());
			}
			if (criteria.getCellphone() != null
					&& !criteria.getCellphone().trim().isEmpty()) {
				whereSql.append(" and cellphone like ? ");
				params.add("%" + criteria.getCellphone() + "%");
			}
			if (criteria.getEmail() != null
					&& !criteria.getEmail().trim().isEmpty()) {
				whereSql.append(" and email like ? ");
				params.add("%" + criteria.getEmail() + "%");
			}

			Number num = (Number) qr.query(cntSql.append(whereSql).toString(),
					new ScalarHandler(), params.toArray());
			pb.setTr(num.intValue());

			StringBuilder sql = new StringBuilder("select * from t_customer ");
			StringBuilder limitSql = new StringBuilder(" limit ?,?");

			params.add((pc - 1) * ps);
			params.add(ps);

			List<Customer> beanList = qr.query(
					sql.append(whereSql).append(limitSql).toString(),
					new BeanListHandler<Customer>(Customer.class),
					params.toArray());
			pb.setBeanList(beanList);
			return pb;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
}
